package com.fr.data;

import com.fr.base.Parameter;
import com.fr.general.data.TableDataException;
import com.fr.stable.StringUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.*;

public class ProductQuantityReport extends AbstractTableData {
    // 字段名枚举
    enum FIELD_NAME {
        PRODUCT_ID, PRODUCT_NAME, QUANTITY, QUANTITY_LAST_WEEK, QUANTITY_LAST_YEAR
    }

    // 列名数组，保存程序数据集所有列名
    private String[] columNames;
    // 保存查询得到列值
    private LinkedHashSet<LinkedHashMap<String, Object>> rowData;

    // 构造函数，定义表结构，该表有10个数据列，列名为column#0，column#1，。。。。。。column#9
    public ProductQuantityReport() {
        // 定义tableName参数
//        setDefaultParameters(new Parameter[]{new Parameter("tableName")});
        // 定义程序数据集列名
        this.parameters = new Parameter[]{new Parameter("bgnDate"), new Parameter("endDate"), new Parameter("productSize"),
                new Parameter("cityId"), new Parameter("partyId")};
        // 填充字段名
        columNames = new String[FIELD_NAME.values().length];
        int i = 0;
        for (FIELD_NAME fieldName : FIELD_NAME.values()) {
            columNames[i] = fieldName.toString();
            i++;
        }
    }

    @Override
    public int getColumnCount() throws TableDataException {
        return columNames.length;
    }

    @Override
    public String getColumnName(int columnIndex) throws TableDataException {
        return columNames[columnIndex];
    }

    @Override
    public int getRowCount() throws TableDataException {
        queryData();
        return rowData.size();
    }

    @Override
    public Object getValueAt(int rowIndex, int columnIndex) {
        queryData();
        int tempRowIndex = 0;
        for (LinkedHashMap<String, Object> row : rowData) {
            if (tempRowIndex == rowIndex) {
                return row.get(columNames[columnIndex]);
            }
            tempRowIndex += 1;
        }
        return null;
    }

    // 准备数据
    public void queryData() {
        // 确保只被执行一次
        if (rowData != null) {
            return;
        }
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMdd");

        // 保存得到的数据库表名
        String bgnDate = parameters[0].getValue().toString();
        String endDate = parameters[1].getValue().toString();
        String productSize = parameters[2].getValue().toString();
        String cityId = parameters[3].getValue().toString();
        String partyId = parameters[4].getValue().toString();
//        String bgnDate = "Thu Sep 13 2018 00:00:00 GMT+0800";
//        String endDate = "Thu Sep 23 2018 00:00:00 GMT+0800";
//        String productSize = "20";
//        String cityId = "1-1','2-2','1117','1118";
//        String partyId = "55000001','55000011','55000002','55000003";
        //开始时间
        Date bgnDateTime = new Date(bgnDate);
//        结束时间
        Date endDateTime = new Date(endDate);
        Calendar beginCa = Calendar.getInstance();
        beginCa.setTime(bgnDateTime);
        String beginCaTime = dateFormat.format(beginCa.getTime());
        Calendar endCa = Calendar.getInstance();
        endCa.setTime(endDateTime);
        String endCaTime = dateFormat.format(endCa.getTime());
        int compareTime = endCa.get(Calendar.DAY_OF_YEAR) - beginCa.get(Calendar.DAY_OF_YEAR);

        //获得上期时间
        beginCa.add(Calendar.DATE, -1);
        String lastWeekEndDate = dateFormat.format(beginCa.getTime());
        beginCa.add(Calendar.DATE, -compareTime);
        String lastWeekBeginDate = dateFormat.format(beginCa.getTime());
        //获得上年时间
        beginCa.setTime(bgnDateTime);
        beginCa.add(Calendar.YEAR, -1);
        String lastYearBeginDate = dateFormat.format(beginCa.getTime());
        endCa.add(Calendar.YEAR, -1);
        String lastYearEndDate = dateFormat.format(endCa.getTime());

        StringBuffer conditionSql = new StringBuffer();
        if (StringUtils.isNotBlank(cityId)) {
            conditionSql.append(" and v.dim_id in('" + cityId + "')");
        }
        if (StringUtils.isNotBlank(partyId)) {
            conditionSql.append(" and v.party_id in('" + partyId + "')");
        }

        // 构造SQL语句,并打印出来

        // 保存得到的结果集
        rowData = new LinkedHashSet<LinkedHashMap<String, Object>>();
        // 下面开始建立数据库连接，按照刚才的SQL语句进行查询
        Connection conn = ConnectUtil.getConnection();
        try {
            Statement stmt = conn.createStatement();
            StringBuffer productList = new StringBuffer();
            //==========================================今年本周数据
            List yearWeekDataList = new ArrayList();
            //查询今年本周本城市数据
            StringBuffer querySqlThisWeek = new StringBuffer();
            querySqlThisWeek.append("SELECT T.PRODUCT_ID, T.PRODUCT_NAME,T.QUANTITY from (");
            querySqlThisWeek.append(" SELECT v.product_id, p.product_name,SUM(V.EXTEND7) QUANTITY ");
            querySqlThisWeek.append("  FROM ZDEV_PRODUCTS_H_V2 V");
            querySqlThisWeek.append("  LEFT JOIN PRODUCT P ON P.PRODUCT_ID = V.PRODUCT_ID");
            querySqlThisWeek.append("  WHERE V.DIM_TYPE = '01' ");
            querySqlThisWeek.append("  AND  v.extend1 >= '" + beginCaTime + "' and  v.extend1 <= '" + endCaTime + "' ");
            querySqlThisWeek.append(conditionSql);
            querySqlThisWeek.append("  GROUP BY v.product_id, p.product_name ORDER BY SUM(V.EXTEND7) desc");
            querySqlThisWeek.append(" ) T where ROWNUM <= " + productSize + "");
            querySqlThisWeek.append(" UNION ALL ");
            querySqlThisWeek.append("SELECT '0000','其他',SUM(temp.QUANTITY) QUANTITY FROM (");
            querySqlThisWeek.append(" SELECT  T.product_id, T.product_name,ROWNUM rn,T.QUANTITY from (");
            querySqlThisWeek.append("  SELECT  v.product_id, p.product_name, SUM(V.EXTEND7) QUANTITY");
            querySqlThisWeek.append("  FROM ZDEV_PRODUCTS_H_V2 V");
            querySqlThisWeek.append("  LEFT JOIN PRODUCT P ON P.PRODUCT_ID = V.PRODUCT_ID");
            querySqlThisWeek.append(" WHERE   V.DIM_TYPE = '01'");
            querySqlThisWeek.append("  AND  v.extend1 >= '" + beginCaTime + "' and  v.extend1 <= '" + endCaTime + "' ");
            querySqlThisWeek.append(conditionSql);
            querySqlThisWeek.append("  GROUP BY v.product_id, p.product_name ORDER BY SUM(V.EXTEND7) desc");
            querySqlThisWeek.append(" ) T  ) temp  WHERE temp.rn > " + productSize + "");
            ResultSet resultSetThisWeek = stmt.executeQuery(querySqlThisWeek.toString());
            //今年本周
            LinkedHashMap<String, Object> thisYearWeekMap;
            while (resultSetThisWeek.next()) {
                thisYearWeekMap = new LinkedHashMap();
                String productId = resultSetThisWeek.getString("PRODUCT_ID");
                if (!"0000".equals(productId)) {
                    productList.append("'" + productId + "',");
                }
                thisYearWeekMap.put(FIELD_NAME.PRODUCT_ID.toString(), resultSetThisWeek.getString("PRODUCT_ID"));
                thisYearWeekMap.put(FIELD_NAME.PRODUCT_NAME.toString(), resultSetThisWeek.getString("PRODUCT_NAME"));
                thisYearWeekMap.put(FIELD_NAME.QUANTITY.toString(), resultSetThisWeek.getBigDecimal("QUANTITY"));
                yearWeekDataList.add(thisYearWeekMap);
            }
            resultSetThisWeek.close();
            // ==========================================今年上周数据

            //查询上周数据
            StringBuffer yearLastWeekData = new StringBuffer();
            yearLastWeekData.append("SELECT T.PRODUCT_ID, T.PRODUCT_NAME,T.QUANTITY from (");
            yearLastWeekData.append(" SELECT v.product_id, p.product_name,SUM(V.EXTEND7) QUANTITY ");
            yearLastWeekData.append("  FROM ZDEV_PRODUCTS_H_V2 V");
            yearLastWeekData.append("  LEFT JOIN PRODUCT P ON P.PRODUCT_ID = V.PRODUCT_ID");
            yearLastWeekData.append("  WHERE V.DIM_TYPE = '01' ");
            yearLastWeekData.append("  AND  v.extend1 >= '" + lastWeekBeginDate + "' and  v.extend1 <= '" + lastWeekEndDate + "' ");
            yearLastWeekData.append("  AND v.product_id in (" + productList.toString().substring(0, productList.toString().length() - 1) + ")");
            yearLastWeekData.append(conditionSql);
            yearLastWeekData.append("  GROUP BY v.product_id, p.product_name ORDER BY SUM(V.EXTEND7) desc");
            yearLastWeekData.append(" ) T");
            yearLastWeekData.append(" UNION ALL ");
            yearLastWeekData.append(" SELECT  '0000','其他',sum(T.QUANTITY) QUANTITY from (");
            yearLastWeekData.append("  SELECT  v.product_id, p.product_name, SUM(V.EXTEND7) QUANTITY");
            yearLastWeekData.append("  FROM ZDEV_PRODUCTS_H_V2 V");
            yearLastWeekData.append("  LEFT JOIN PRODUCT P ON P.PRODUCT_ID = V.PRODUCT_ID");
            yearLastWeekData.append(" WHERE   V.DIM_TYPE = '01'");
            yearLastWeekData.append("  AND v.product_id not in (" + productList.toString().substring(0, productList.toString().length() - 1) + ")");
            yearLastWeekData.append("  AND  v.extend1 >= '" + lastWeekBeginDate + "' and  v.extend1 <= '" + lastWeekEndDate + "' ");
            yearLastWeekData.append(conditionSql);
            yearLastWeekData.append("   GROUP BY V.PRODUCT_ID, P.PRODUCT_NAME )T");
            ResultSet lastWeekRs = stmt.executeQuery(yearLastWeekData.toString());
            List lastWeekDataList = new ArrayList();
            LinkedHashMap<String, Object> thisYearLastWeekMap;
            while (lastWeekRs.next()) {
                //合计
                //上周每天数据
                thisYearLastWeekMap = new LinkedHashMap<String, Object>();
                thisYearLastWeekMap.put(FIELD_NAME.PRODUCT_ID.toString(), lastWeekRs.getString("PRODUCT_ID"));
                thisYearLastWeekMap.put(FIELD_NAME.PRODUCT_NAME.toString(), lastWeekRs.getString("PRODUCT_NAME"));
                thisYearLastWeekMap.put(FIELD_NAME.QUANTITY_LAST_WEEK.toString(), lastWeekRs.getBigDecimal("QUANTITY"));
                lastWeekDataList.add(thisYearLastWeekMap);
            }
            lastWeekRs.close();

            // ==========================================去年数据
            //查询去年数据
            StringBuffer lastYearCityData = new StringBuffer();
            //去年
            lastYearCityData.append("SELECT T.PRODUCT_ID, T.PRODUCT_NAME,T.QUANTITY from (");
            lastYearCityData.append(" SELECT v.product_id, p.product_name,SUM(V.EXTEND7) QUANTITY ");
            lastYearCityData.append("  FROM ZDEV_PRODUCTS_H_V2 V");
            lastYearCityData.append("  LEFT JOIN PRODUCT P ON P.PRODUCT_ID = V.PRODUCT_ID");
            lastYearCityData.append("  WHERE V.DIM_TYPE = '01' ");
            lastYearCityData.append("  AND  v.extend1 >= '" + lastYearBeginDate + "' and  v.extend1 <= '" + lastYearEndDate + "' ");
            lastYearCityData.append("  AND v.product_id in (" + productList.toString().substring(0, productList.toString().length() - 1) + ")");
            lastYearCityData.append(conditionSql);
            lastYearCityData.append("  GROUP BY v.product_id, p.product_name ORDER BY SUM(V.EXTEND7) desc");
            lastYearCityData.append(" )T");
            lastYearCityData.append(" UNION ALL ");
            lastYearCityData.append(" SELECT  '0000','其他',sum(T.QUANTITY) QUANTITY from (");
            lastYearCityData.append("  SELECT  v.product_id, p.product_name, SUM(V.EXTEND7) QUANTITY");
            lastYearCityData.append("  FROM ZDEV_PRODUCTS_H_V2 V");
            lastYearCityData.append("  LEFT JOIN PRODUCT P ON P.PRODUCT_ID = V.PRODUCT_ID");
            lastYearCityData.append(" WHERE   V.DIM_TYPE = '01'");
            lastYearCityData.append("  AND v.product_id not in (" + productList.toString().substring(0, productList.toString().length() - 1) + ")");
            lastYearCityData.append("  AND  v.extend1 >= '" + lastYearBeginDate + "' and  v.extend1 <= '" + lastYearEndDate + "' ");
            lastYearCityData.append(conditionSql);
            lastYearCityData.append("   GROUP BY V.PRODUCT_ID, P.PRODUCT_NAME )T");
            ResultSet lastYearRs = stmt.executeQuery(lastYearCityData.toString());
            List lastYearDataList = new ArrayList();
            LinkedHashMap<String, Object> lastYearMap;
            while (lastYearRs.next()) {
                //合计
                //上周每天数据
                lastYearMap = new LinkedHashMap<String, Object>();
                lastYearMap.put(FIELD_NAME.PRODUCT_ID.toString(), lastYearRs.getString("PRODUCT_ID"));
                lastYearMap.put(FIELD_NAME.PRODUCT_NAME.toString(), lastYearRs.getString("PRODUCT_NAME"));
                lastYearMap.put(FIELD_NAME.QUANTITY_LAST_YEAR.toString(), lastYearRs.getBigDecimal("QUANTITY"));
                lastYearDataList.add(lastYearMap);
            }
            lastYearRs.close();
            //合并今年本周、上周、去年本周数据
            for (int i = 0; i < yearWeekDataList.size(); i++) {
                LinkedHashMap<String, Object> map1 = (LinkedHashMap<String, Object>) yearWeekDataList.get(i);
                for (int j = 0; j < lastWeekDataList.size(); j++) {
                    LinkedHashMap<String, Object> map2 = (LinkedHashMap<String, Object>) lastWeekDataList.get(j);
                    if (map1.get(FIELD_NAME.PRODUCT_ID.toString()).equals(map2.get(FIELD_NAME.PRODUCT_ID.toString()))) {
                        map1.put(FIELD_NAME.QUANTITY_LAST_WEEK.toString(), map2.get(FIELD_NAME.QUANTITY_LAST_WEEK.toString()));
                    }
                }

                for (int j = 0; j < lastYearDataList.size(); j++) {
                    LinkedHashMap<String, Object> map3 = (LinkedHashMap<String, Object>) lastYearDataList.get(j);
                    if (map1.get(FIELD_NAME.PRODUCT_ID.toString()).equals(map3.get(FIELD_NAME.PRODUCT_ID.toString()))) {
                        map1.put(FIELD_NAME.QUANTITY_LAST_YEAR.toString(), map3.get(FIELD_NAME.QUANTITY_LAST_YEAR.toString()));
                    }
                }
            }

            for (int i = 0; i < yearWeekDataList.size(); i++) {
                LinkedHashMap<String, Object> linkedHashMap = (LinkedHashMap) yearWeekDataList.get(i);
                LinkedHashMap<String, Object> row = new LinkedHashMap<String, Object>();
                // 注：字段赋值的顺序，要跟枚举里的顺序一样
                row.put(FIELD_NAME.PRODUCT_ID.toString(), linkedHashMap.get(FIELD_NAME.PRODUCT_ID.toString()));
                row.put(FIELD_NAME.PRODUCT_NAME.toString(), linkedHashMap.get(FIELD_NAME.PRODUCT_NAME.toString()));
                row.put(FIELD_NAME.QUANTITY.toString(), linkedHashMap.get(FIELD_NAME.QUANTITY.toString()));
                row.put(FIELD_NAME.QUANTITY_LAST_WEEK.toString(), linkedHashMap.get(FIELD_NAME.QUANTITY_LAST_WEEK.toString()));
                row.put(FIELD_NAME.QUANTITY_LAST_YEAR.toString(), linkedHashMap.get(FIELD_NAME.QUANTITY_LAST_YEAR.toString()));
                rowData.add(row);
            }
            System.out.println(rowData.toString());
            // 释放数据库资源
            stmt.close();
            conn.close();

        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    // 释放一些资源，因为可能会有重复调用，所以需释放valueList，将上次查询的结果释放掉
    @Override
    public void release() throws Exception {
        super.release();
        this.rowData = null;
    }
}
